from pymysql import Connection
from datetime import datetime


# 日志管理系统主类
class LogManagementSystem:
    def __init__(self):
        self.conn = None

    def connect_db(self):
        """连接数据库"""
        try:
            self.conn = Connection(
                host='localhost',
                port=3306,
                user='root',
                password='root',
                database='db3',
                charset='utf8'
            )
            return self.conn
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return None

    def close_connection(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None

    def create_table(self):
        """创建数据库表"""
        if not self.conn:
            print("数据库未连接")
            return False

        cursor = self.conn.cursor()

        # 创建数据表
        create_log_type_sql = """
                              create table if not exists log_type(
                                  id int primary key auto_increment,
                                  type varchar(20),
                                  type_description text,
                                  level int not null comment '1-低, 2-中, 3-高, 4-极高'
                                  ) 
                              """

        create_journal_sql = """
                             create table if not exists journal(
                                 Jid int primary key auto_increment,
                                 occurrenceTime datetime,
                                 log_type_id int,
                                 details text, 
                                 FOREIGN KEY(log_type_id) REFERENCES log_type(id)
                             ) 
                             """
        try:
            cursor.execute(create_log_type_sql)  # 创建日志类型表
            cursor.execute(create_journal_sql)  # 创建日志记录表
            self.conn.commit()
            print("表创建成功！已建立外键关联关系。")
            return True
        except Exception as e:
            self.conn.rollback()
            print(f"创建表失败: {e}")
            return False
        finally:
            cursor.close()

    def init_log_type_data(self):
        """初始化日志类型表数据"""
        if not self.conn:
            print("数据库未连接")
            return False

        cursor = self.conn.cursor()
        # 初始化类型
        log_types = [
            ('ERROR', '系统错误', 3),  # 高 -> 3
            ('WARN', '警告信息', 2),  # 中 -> 2
            ('INFO', '普通信息', 1),  # 低 -> 1
            ('DEBUG', '调试日志', 1),  # 低 -> 1
            ('CRITICAL', '严重故障', 4)  # 极高 -> 4
        ]
        insert_sql = """
                     insert into log_type (type, type_description, level)
                     values (%s, %s, %s) on duplicate key
                     update type_description = values (type_description),
                     level = values (level)
                     """
        try:
            # 插入数据（如果已存在则更新）
            result = cursor.executemany(insert_sql, log_types)
            self.conn.commit()

            print(f"日志类型表数据初始化完成！影响 {result} 行记录")
            print(f"共插入 {len(log_types)} 条记录")

            # 验证插入的数据
            cursor.execute("SELECT * FROM log_type ORDER BY id")
            results = cursor.fetchall()

            level_map = {1: '低', 2: '中', 3: '高', 4: '极高'}
            print("\n当前日志类型表数据：")
            print("-" * 50)
            print(f"{'ID':<4} {'类型名称':<10} {'类型说明':<12} {'级别':<6} {'级别文本':<8}")
            print("-" * 50)
            for row in results:
                level_text = level_map.get(row[3], '未知')
                print(f"{row[0]:<4} {row[1]:<10} {row[2]:<12} {row[3]:<6} {level_text:<8}")
            print("-" * 50)
            return True

        except Exception as e:
            self.conn.rollback()
            print(f"数据初始化失败: {e}")
            import traceback
            traceback.print_exc()
            return False
        finally:
            cursor.close()

    def display_menu(self):
        """显示菜单"""
        print("日志管理系统")
        print("=" * 50)
        print("1. 浏览所有日志记录")
        print("2. 添加日志记录")
        print("3. 删除日志记录")
        print("4. 按时间段查询日志")
        print("5. 查看高严重级别日志")
        print("6. Top 3最频繁错误消息")
        print("7. 按级别统计日志数量")
        print("8. 退出系统")
        print("=" * 50)

    def browse_all_logs(self):
        """浏览所有日志记录"""
        try:
            cursor = self.conn.cursor()
            query = """
                    SELECT j.Jid,
                           j.occurrenceTime,
                           j.details,
                           t.type,
                           t.type_description,
                           t.level
                    FROM journal j
                             JOIN log_type t ON j.log_type_id = t.id
                    ORDER BY j.occurrenceTime DESC
                    """
            cursor.execute(query)
            logs = cursor.fetchall()

            if not logs:
                print("暂无日志记录")
                return

            print("\n所有日志记录:")
            print("-" * 100)
            print(f"{'ID':<4} {'发生时间':<20} {'日志类型':<10} {'级别':<6} {'详情':<40}")
            print("-" * 100)

            for log in logs:
                log_id, time, details, log_type, description, level = log
                level_text = self.get_level_text(level)
                print(f"{log_id:<4} {time.strftime('%Y-%m-%d %H:%M:%S'):<20} "
                      f"{log_type:<10} {level_text:<6} {details[:40]:<40}")

            print(f"\n共找到 {len(logs)} 条日志记录")

        except Exception as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()

    def add_log_record(self):
        """添加日志记录"""
        try:
            cursor = self.conn.cursor()

            # 显示可选的日志类型
            cursor.execute("SELECT id, type, type_description, level FROM log_type")
            log_types = cursor.fetchall()

            print("\n可选的日志类型:")
            print("-" * 50)
            for i, (type_id, type_name, description, level) in enumerate(log_types, 1):
                level_text = self.get_level_text(level)
                print(f"{i}. {type_name} ({description}) - 级别: {level_text}")

            # 选择日志类型
            try:
                choice = int(input("\n请选择日志类型编号: "))
                if 1 <= choice <= len(log_types):
                    selected_type = log_types[choice - 1]
                    log_type_id = selected_type[0]
                else:
                    print("无效的选择")
                    return
            except ValueError:
                print("请输入有效的数字")
                return

            # 输入日志详情
            details = input("请输入日志详情: ").strip()
            if not details:
                print("日志详情不能为空")
                return

            # 插入日志记录
            insert_sql = """
                         insert into journal (occurrenceTime, log_type_id, details)
                         values (%s, %s, %s)
                         """
            current_time = datetime.now()

            cursor.execute(insert_sql, (current_time, log_type_id, details))
            self.conn.commit()

            print(f"日志添加成功！时间: {current_time}")

        except Exception as e:
            self.conn.rollback()
            print(f"添加日志失败: {e}")
        finally:
            cursor.close()

    def delete_log_record(self):
        """根据ID删除日志记录"""
        try:
            log_id = input("请输入要删除的日志ID: ").strip()
            if not log_id.isdigit():
                print("请输入有效的数字ID")
                return

            cursor = self.conn.cursor()

            # 先查询确认日志存在
            cursor.execute("SELECT Jid, occurrenceTime, details FROM journal WHERE Jid = %s", (log_id,))
            log_record = cursor.fetchone()

            if not log_record:
                print(f"ID为 {log_id} 的日志记录不存在")
                return

            print(f"找到日志记录: ID={log_record[0]}, 时间={log_record[1]}, 详情={log_record[2]}")

            # 确认删除
            confirm = input("确认删除吗？(y/n): ").lower()
            if confirm == 'y':
                cursor.execute("DELETE FROM journal WHERE Jid = %s", (log_id,))
                self.conn.commit()
                print("日志删除成功！")
            else:
                print("取消删除操作")

        except Exception as e:
            self.conn.rollback()
            print(f"删除日志失败: {e}")
        finally:
            cursor.close()

    def query_logs_by_time_range(self):
        """查询某时间段内的所有日志"""
        try:
            print("\n请输入查询的时间范围:")
            start_time_str = input("开始时间 (格式: YYYY-MM-DD HH:MM:SS): ")
            end_time_str = input("结束时间 (格式: YYYY-MM-DD HH:MM:SS): ")

            # 转换时间格式
            try:
                start_time = datetime.strptime(start_time_str, "%Y-%m-%d %H:%M:%S")
                end_time = datetime.strptime(end_time_str, "%Y-%m-%d %H:%M:%S")
            except ValueError:
                print("时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式")
                return

            cursor = self.conn.cursor()
            query = """
                    SELECT j.Jid,
                           j.occurrenceTime,
                           j.details,
                           t.type,
                           t.type_description,
                           t.level
                    FROM journal j
                             JOIN log_type t
                                  ON j.log_type_id = t.id
                    WHERE j.occurrenceTime between %s and %s
                    ORDER BY j.occurrenceTime DESC 
                    """

            cursor.execute(query, (start_time, end_time))
            logs = cursor.fetchall()

            if not logs:
                print(f"在 {start_time} 到 {end_time} 时间段内没有找到日志记录")
                return

            print(f"\n在 {start_time} 到 {end_time} 时间段的日志记录:")
            print("-" * 120)
            print(f"{'ID':<4} {'发生时间':<20} {'日志类型':<10} {'级别':<6} {'类型说明':<15} {'详情':<50}")
            print("-" * 120)

            for log in logs:
                log_id, time, details, log_type, description, level = log
                level_text = self.get_level_text(level)
                print(f"{log_id:<4} {time.strftime('%Y-%m-%d %H:%M:%S'):<20} "
                      f"{log_type:<10} {level_text:<6} {description:<15} {details[:50]:<50}")

            print(f"\n共找到 {len(logs)} 条日志记录")

        except Exception as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()

    def view_high_severity_logs(self):
        """查看严重级别为'极高'或'高'的日志"""
        try:
            cursor = self.conn.cursor()
            query = """
                    SELECT j.Jid,
                           j.occurrenceTime,
                           j.details,
                           t.type,
                           t.type_description,
                           t.level
                    FROM journal j
                             JOIN log_type t
                                  ON j.log_type_id = t.id
                    WHERE t.level
                              IN (3, 4) -- 3=高, 4=极高
                    ORDER BY t.level DESC, j.occurrenceTime DESC 
                    """

            cursor.execute(query)
            logs = cursor.fetchall()

            if not logs:
                print("没有找到高严重级别的日志记录")
                return

            print("\n高严重级别日志记录:")
            print("-" * 120)
            print(f"{'ID':<4} {'发生时间':<20} {'日志类型':<10} {'级别':<6} {'类型说明':<15} {'详情':<50}")
            print("-" * 120)

            for log in logs:
                log_id, time, details, log_type, description, level = log
                level_text = self.get_level_text(level)
                print(f"{log_id:<4} {time.strftime('%Y-%m-%d %H:%M:%S'):<20} "
                      f"{log_type:<10} {level_text:<6} {description:<15} {details[:50]:<50}")

            print(f"\n共找到 {len(logs)} 条高严重级别日志记录")

        except Exception as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()

    def find_top3_frequent_errors(self):
        """找出Top 3最频繁出现的错误消息"""
        try:
            cursor = self.conn.cursor()
            query = """
                    SELECT details, COUNT(*) as count
                    FROM journal
                    GROUP BY details
                    ORDER BY count DESC
                        LIMIT 3 \
                    """

            cursor.execute(query)
            top_errors = cursor.fetchall()

            if not top_errors:
                print("没有找到错误消息")
                return

            print("\nTop 3 最频繁出现的错误消息:")
            print("-" * 80)
            print(f"{'排名':<4} {'出现次数':<8} {'错误消息':<60}")
            print("-" * 80)

            for i, (details, count) in enumerate(top_errors, 1):
                print(f"{i:<4} {count:<8} {details[:60]:<60}")

        except Exception as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()

    def count_logs_by_level(self):
        """按级别统计日志数量"""
        try:
            cursor = self.conn.cursor()
            query = """
                    SELECT t.level, t.type, t.type_description, COUNT(j.Jid) as count
                    FROM log_type t
                        LEFT JOIN journal j
                    ON t.id = j.log_type_id
                    GROUP BY t.id, t.level, t.type, t.type_description
                    ORDER BY t.level DESC
                    """

            cursor.execute(query)
            level_stats = cursor.fetchall()

            if not level_stats:
                print("没有找到日志统计信息")
                return

            print("\n按级别统计日志数量:")
            print("-" * 60)

            for level, log_type, description, count in level_stats:
                level_text = self.get_level_text(level)
                type_display = f"{description}[{log_type}]"
                print(f"{type_display:<20} {count}条")

        except Exception as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()

    def get_level_text(self, level):
        """将级别数字转换为文本描述"""
        level_map = {1: '低', 2: '中', 3: '高', 4: '极高'}
        return level_map.get(level, '未知')

    def run(self):
        """运行主程序"""
        print("系统开始运行")

        # 连接数据库
        if not self.connect_db():
            return

        # 创建表和初始化数据
        if not self.create_table():
            self.close_connection()
            return

        if not self.init_log_type_data():
            self.close_connection()
            return

        try:
            while True:
                self.display_menu()
                choice = input("请选择操作 (1-8): ").strip()

                if choice == '1':
                    self.browse_all_logs()
                elif choice == '2':
                    self.add_log_record()
                elif choice == '3':
                    self.delete_log_record()
                elif choice == '4':
                    self.query_logs_by_time_range()
                elif choice == '5':
                    self.view_high_severity_logs()
                elif choice == '6':
                    self.find_top3_frequent_errors()
                elif choice == '7':
                    self.count_logs_by_level()
                elif choice == '8':
                    print("感谢使用日志管理系统")
                    break
                else:
                    print("无效的选择，请重新输入")

                input("\n按回车键继续...")

        finally:
            self.close_connection()


# 主程序入口
if __name__ == "__main__":
    print("启动日志管理系统...")
    system = LogManagementSystem()
    system.run()